package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

/**
 * Make the updates, either DELETE, UPDATE and also INSERT
 * 
 * @author Cassio Soares Cabral
 * 
 */
public class Update {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DATABASE_URL = "jdbc:mysql://localhost/marketingjoy";

	static String USER = utils.Config.getDBUserName();
	static String PASSWORD = utils.Config.getDBPassword();

	/**
	 * Method update, which is the responsible for the UPDATE, DELETE or INSERT.
	 * 
	 * @param SQLCommand
	 */
	public static void update(String SQLCommand)
			throws MySQLIntegrityConstraintViolationException {
		{
			Connection connection = null; // manages connection
			Statement statement = null; // query statement

			// connect to database books and query database
			try {
				// load database driver class
				Class.forName(JDBC_DRIVER);
				// establish connection to database
				connection = DriverManager.getConnection(DATABASE_URL, USER,
						PASSWORD);
				// create Statement for querying database
				statement = connection.createStatement();
				// query database
				statement.executeUpdate(SQLCommand);
			} // end try
			catch (SQLException sqlException) {
				sqlException.printStackTrace();
			} // end catch
			catch (ClassNotFoundException classNotFound) {
				classNotFound.printStackTrace();
			}
			// end catch
			finally // ensure statement and connection are closed properly
			{
				try {
					statement.close();
					connection.close();
				} // end try
				catch (Exception exception) {
					exception.printStackTrace();
				} // end catch
			} // end finally
		}
	}

	/**
	 * Remove a sponsor from database.
	 * 
	 * @param nameOfSponsor
	 * 
	 */
	public void removeSponsorFromBD(String nameOfSponsor) {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement

		// connect to database books and query database
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// query database
			statement.executeUpdate("DELETE FROM sponsor WHERE name = '"
					+ nameOfSponsor + "'");
		} // end try
		catch (SQLException sqlException) {
			sqlException.printStackTrace();
		} // end catch
		catch (ClassNotFoundException classNotFound) {
			classNotFound.printStackTrace();
		} // end catch
		finally // ensure statement and connection are closed properly
		{
			try {
				statement.close();
				connection.close();
			} // end try
			catch (Exception exception) {
				exception.printStackTrace();

			} // end catch
		} // end finally
	}

	/**
	 * Remove a feed from database.
	 * 
	 * @param feed
	 * 
	 */
	public void removeFeedFromDB(String feed) {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement

		// connect to database books and query database
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// query database
			statement.executeUpdate("DELETE FROM rss WHERE feed = '" + feed
					+ "'");
		} // end try
		catch (SQLException sqlException) {
			sqlException.printStackTrace();
		} // end catch
		catch (ClassNotFoundException classNotFound) {
			classNotFound.printStackTrace();
		} // end catch
		finally // ensure statement and connection are closed properly
		{
			try {
				statement.close();
				connection.close();
			} // end try
			catch (Exception exception) {
				exception.printStackTrace();

			} // end catch
		} // end finally

	}

	/**
	 * Remove a feed from database.
	 * 
	 * @param path
	 * 
	 */
	public void removeVideoFromDB(String path) {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement

		// connect to database books and query database
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// query database
			statement.executeUpdate("DELETE FROM video WHERE path = '" + path
					+ "'");
		} // end try
		catch (SQLException sqlException) {
			sqlException.printStackTrace();
		} // end catch
		catch (ClassNotFoundException classNotFound) {
			classNotFound.printStackTrace();
		} // end catch
		finally // ensure statement and connection are closed properly
		{
			try {
				statement.close();
				connection.close();
			} // end try
			catch (Exception exception) {
				exception.printStackTrace();

			} // end catch
		} // end finally
	}

	/**
	 * Update the sponsor type.
	 * 
	 * @param sponsorName
	 * @param newType
	 */
	public void updateSponsorType(String sponsorName, String newType) {
		try {
			update("UPDATE sponsor SET type = '" + newType + "' WHERE name = '"
					+ sponsorName + "'");
		} catch (MySQLIntegrityConstraintViolationException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Update the sponsor name.
	 * 
	 * @param sponsorNameNow
	 * @param futureSponsorName
	 */
	public void updateSponsorName(String sponsorNameNow,
			String futureSponsorName) {
		try {
			update("UPDATE sponsor SET name = '" + futureSponsorName
					+ "' WHERE name = '" + sponsorNameNow + "'");
		} catch (MySQLIntegrityConstraintViolationException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Update the sponsor monthly.
	 * 
	 * @param sponsorName
	 * @param newMonthly
	 */
	public void updateSponsorMonthly(String sponsorName, Double newMonthly) {
		try {
			update("UPDATE sponsor SET monthly = '" + newMonthly
					+ "' WHERE name = '" + sponsorName + "'");
		} catch (MySQLIntegrityConstraintViolationException e) {
			e.printStackTrace();
		}
	}

}
